package org.jumiz.db.util;

import org.jumiz.db.doc.bean.ColumnOnLine;
import org.jumiz.db.doc.bean.TableOnLine;
import org.jumiz.db.config.MySQLConfig;
import org.jumiz.db.doc.bean.DocBeanOnLine;
import org.jumiz.db.result.TypeResultZ;
import org.jumiz.db.type.TypeDBZ;

import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public final class DocDBOnLineUtilZ {
	
	private static String absPath = null;

	public static MySQLConfig config;

	private static String database;

	private static Connection getConnection() {
		try {
			Class.forName(config.getDriverClass());
			return DriverManager.getConnection(config.getUrl());
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public static void init() {
		if (database == null) {
			String sql = "select database() as database_name";
			Connection connection = getConnection();
			try {
				PreparedStatement preparedStatement = connection.prepareStatement(sql);
				ResultSet resultSet = preparedStatement.executeQuery();
				if (resultSet.next()) {
					database = resultSet.getString("database_name");
				}
				resultSet.close();
				preparedStatement.close();
				connection.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	public String doc(TypeResultZ type) throws SQLException {
		if(TypeResultZ.JSON.equals(type)) {
			return json();
		}
		if(TypeResultZ.HTML.equals(type)) {
			return html();
		}
		return null;
	}
	
	private String html() throws SQLException {
		StringBuilder sb = new StringBuilder("<!DOCTYPE html><html><head><meta charset=\"UTF-8\"><title>JumiZ</title><style>table,td,th {border:1px solid #999;} th {background:#ccc;}</style></head><body>");
		List<DocBeanOnLine> list = tables();
		for(int i = 0, len = list.size(); i < len; i++) {
			DocBeanOnLine b = list.get(i);
			TableOnLine t = b.getTableOnLine();
			sb.append("<h2>").append(i + 1).append("、").append(t.nameC).append("(").append(t.nameE).append(")").append("</h2>")
			.append(t.desC).append("<br>").append("<br>");
			;
			sb.append("<table cellspacing=\"0\" style='width:100%;'><tr><th>字段名</th><th>中文名</th><th>数据类型</th><th>是否为空</th><th style='max-width:25%;width:25%;'>备注</th></tr>");
			List<ColumnOnLine> fs = b.getColumnOnLines();
			for(ColumnOnLine f : fs) {
				sb.append("<tr>")
				.append("<td>").append(f.nameE).append("</td>")
				.append("<td>").append(f.nameC).append("</td>")
				.append("<td>").append(f.columnType.toUpperCase()).append("</td>")
				.append("<td>").append(f.isNull).append("</td>")
				.append("<td>").append(f.desC).append("</td>")
				.append("</tr>");
			}
			sb.append("</table>");
		}
		sb.append("</body></html>");
		return sb.toString();
	}

	private List<DocBeanOnLine> tables() throws SQLException {
		Connection connection = getConnection();
		PreparedStatement preparedStatement = connection.prepareStatement("select table_name, table_comment from information_schema.tables where table_schema = ? ;");
		preparedStatement.setString(1, database);
		ResultSet resultSet = preparedStatement.executeQuery();
		List<DocBeanOnLine> result = new ArrayList<>();
		while (resultSet.next()) {
			String tableName = resultSet.getString("table_name");
			String tableComment = resultSet.getString("table_comment");
			String tableNameCn = null;
			ParamIntString parse = parse(tableComment);
			if (parse != null) {
				tableNameCn = tableComment.substring(0, parse.getInt());
				tableComment = tableComment.substring(parse.getInt() + 1);
			} else {
				tableNameCn = tableComment;
			}
			TableOnLine tableOnLine = new TableOnLine(tableName, tableNameCn, tableComment);
			List<ColumnOnLine> columnOnLines = columns(tableName);
			DocBeanOnLine docBeanOnLine = new DocBeanOnLine(tableOnLine, columnOnLines);
			result.add(docBeanOnLine);
		}
		resultSet.close();
		preparedStatement.close();
		connection.close();
		return result;
	}

	private List<ColumnOnLine> columns(String tableName) throws SQLException {
		String sql = "select * from information_schema.COLUMNS where table_schema = ? and table_name = ? order by ordinal_position;";
		Connection connection = getConnection();
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setString(1, database);
		preparedStatement.setString(2, tableName);
		ResultSet resultSet = preparedStatement.executeQuery();
		List<ColumnOnLine> result = new ArrayList<>();
		while (resultSet.next()) {
			String columnName = resultSet.getString("column_name");
			String columnComment = resultSet.getString("column_comment");
			String columnNameCn = null;
			boolean isNull = resultSet.getBoolean("IS_NULLABLE");
			TypeDBZ dataType = TypeDBZ.valueOf(resultSet.getString("DATA_TYPE").toUpperCase());
			ParamIntString parse = parse(columnComment);
			if (parse != null) {
				columnNameCn = columnComment.substring(0, parse.getInt());
				columnComment = columnComment.substring(parse.getInt() + 1);
			} else {
				columnNameCn = columnComment;
			}
			String columnType = resultSet.getString("COLUMN_TYPE");
			ColumnOnLine columnOnLine = new ColumnOnLine(columnName, columnNameCn, columnComment, isNull, dataType, columnType);
			result.add(columnOnLine);
		}
		resultSet.close();
		preparedStatement.close();
		connection.close();
		return result;
	}

	private String json() throws SQLException {
		List<DocBeanOnLine> tables = tables();
		StringBuilder sb = new StringBuilder();
		for(DocBeanOnLine b : tables) {
			TableOnLine t = b.getTableOnLine();
			sb.append("{\"nameE\":\"").append(t.nameE).append("\",")
			.append("\"nameC\":\"").append(t.nameC).append("\",")
			.append("\"desC\":\"").append(t.desC).append("\",")
			.append("\"columnNames\":[")
			;
			List<ColumnOnLine> lis = b.getColumnOnLines();
			for(ColumnOnLine f : lis) {
				sb.append("{\"nameE\":\"").append(f.nameE).append("\",")
				.append("\"nameC\":\"").append(f.nameC).append("\",")
				.append("\"dataType\":\"").append(f.dataType).append("\",")
				.append("\"isNull\":\"").append(f.isNull).append("\",")
				.append("\"desC\":\"").append(f.desC).append("\"},")
				;
			}
			if(sb.lastIndexOf(",") + 1 == sb.length()) {
				sb.deleteCharAt(sb.lastIndexOf(","));
			}
			sb.append("]},");
		}
		if(sb.lastIndexOf(",") > 0 && sb.lastIndexOf(",") + 1 == sb.length()) {
			sb.deleteCharAt(sb.lastIndexOf(","));
		}
		return sb.toString();
	}

	private ParamIntString parse(String source, String separator) {
		int index = source.indexOf(separator);
		if (index < 0) {
			return null;
		}
		ParamIntString paramIntString = new ParamIntString();
		paramIntString.setInt(index);
		paramIntString.setString(separator);
		return paramIntString;
	}

	private ParamIntString parse(String source) {
		List<String> list = Arrays.asList(",", "，", " ");
		ParamIntString min = null;
		for(String separator : list) {
			ParamIntString parse = parse(source, separator);
			if (min == null) {
				min = parse;
			}
			if (parse != null) {
				min = min.getInt() < parse.getInt() ? min : parse;
			}
		}
		return min;
	}

}

class ParamIntString {
	private Integer param1;
	private String param2;

	public void setInt(Integer param1) {
		this.param1 = param1;
	}

	public void setString(String param2) {
		this.param2 = param2;
	}

	public Integer getInt() {
		return param1;
	}

	public String getString() {
		return param2;
	}
}
